﻿Imports MySql.Data.MySqlClient
Public Class frmview_voyemptyn
    Dim mysql As MySqlConnection = main_form.mysqlconection
    Dim mySqlCommand As New MySqlCommand
    Dim mySqlAdaptor As New MySqlDataAdapter
    Dim mySqlReader As MySqlDataReader
    Dim inbtIndex As Integer
    Dim inbtIndex1 As Integer
    Dim count40 As Integer = 0
    Dim count20 As Integer = 0
    Private Sub frmview_voyemptyn_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
        System.Threading.Thread.CurrentThread.CurrentUICulture = System.Threading.Thread.CurrentThread.CurrentCulture

        mysql.Close()

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        mySqlCommand.CommandText = "Select * from voyage_empty where idvoyage_empty ='" & frmmain_voyempty.idvoyage & "';"
        ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand
        Try
            mySqlReader = mySqlCommand.ExecuteReader

            While (mySqlReader.Read())
                date_eta_penang.Text = mySqlReader("etdn")
                date_etd_penang.Text = mySqlReader("etan")
                date_krabi_eta.Text = mySqlReader("etds")
                txt_voyage.Text = mySqlReader("idvoyage")
                date_krabi_etd.Text = mySqlReader("etas")
            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()






        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        mySqlCommand.CommandText = "Select * from voyage_detailn where voyempty ='" & txt_voyage.Text & "';"
        ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand
        Try
            mySqlReader = mySqlCommand.ExecuteReader

            While (mySqlReader.Read())

               

                DataGridViewX1.Rows.Add({mySqlReader("emopern"), Trim(mySqlReader("emnon")), mySqlReader("emtypen"), mySqlReader("embookn"), mySqlReader("emscn"), mySqlReader("embyn"), mySqlReader("emremarkn"), "", "", "", mySqlReader("idvoyage_detailn")})



            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()



    End Sub

    Private Sub DataGridViewX1_CellContentClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridViewX1.CellContentClick

        If e.RowIndex < 0 Then
            Exit Sub
        End If
        Try
            inbtIndex = e.RowIndex
            DataGridViewX1.Rows(inbtIndex).Selected = True
            'MsgBox(DataGridViewX1.Rows(inbtIndex).Cells(9).Value)

        Catch ex As Exception

        End Try

    End Sub

    Private Sub SAVE_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SAVE.Click


        If IsNumeric(DataGridViewX1.Rows(inbtIndex).Cells(9).Value) Then

        Else
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            Try
                mySqlCommand.Parameters.Clear()
                mySqlCommand.CommandText = "insert into voyage_detailn ( emopern, emnon, emtypen,embookn,embyn,emscn,emremarkn,voyempty) values (@emopern,@emnon,@emtypen,@embookn,@embyn,@emscn,@emremarkn,@voyempty)"
                mySqlCommand.Connection = mysql


                mySqlCommand.Parameters.AddWithValue("@emopern", DataGridViewX1.Rows(inbtIndex).Cells(0).Value)
                mySqlCommand.Parameters.AddWithValue("@emnon", DataGridViewX1.Rows(inbtIndex).Cells(1).Value)
                mySqlCommand.Parameters.AddWithValue("@emtypen", DataGridViewX1.Rows(inbtIndex).Cells(2).Value)
                mySqlCommand.Parameters.AddWithValue("@embookn", DataGridViewX1.Rows(inbtIndex).Cells(3).Value)
                mySqlCommand.Parameters.AddWithValue("@embyn", DataGridViewX1.Rows(inbtIndex).Cells(4).Value)
                mySqlCommand.Parameters.AddWithValue("@emscn", DataGridViewX1.Rows(inbtIndex).Cells(5).Value)
                mySqlCommand.Parameters.AddWithValue("@emremarkn", DataGridViewX1.Rows(inbtIndex).Cells(6).Value)

                mySqlCommand.Parameters.AddWithValue("@voyempty", txt_voyage.Text)
                'mySqlCommand.Parameters.AddWithValue("@CTNCODE", DataGridViewX1.Rows(inbtIndex).Cells(3).Value)
                'mySqlCommand.Parameters.AddWithValue("@CTNCODEETC", DataGridViewX1.Rows(inbtIndex).Cells(4).Value)
                mySqlCommand.ExecuteNonQuery()
                mysql.Close()
                MsgBox("บันทึกข้อมูลเรียบร้อย")
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try

        End If

        showgrid()


    End Sub

    Public Sub showgrid()
        mysql.Close()

        DataGridViewX1.Rows.Clear()

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        mySqlCommand.CommandText = "Select * from voyage_detailn where voyempty ='" & txt_voyage.Text & "';"
        ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand
        Try
            mySqlReader = mySqlCommand.ExecuteReader

            While (mySqlReader.Read())

       

                DataGridViewX1.Rows.Add({mySqlReader("emopern"), Trim(mySqlReader("emnon")), mySqlReader("emtypen"), mySqlReader("embookn"), mySqlReader("emscn"), mySqlReader("embyn"), mySqlReader("emremarkn"), "", "", "", mySqlReader("idvoyage_detailn")})



            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()

      

    End Sub

    Private Sub EDIT_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles EDIT.Click



        If IsNumeric(DataGridViewX1.Rows(inbtIndex1).Cells(10).Value) Then

            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            Try
                Dim commandText2 As String
                commandText2 = "UPDATE voyage_detailn SET  emopern = '" & DataGridViewX1.Rows(inbtIndex).Cells(0).Value & "' , emnon = '" & DataGridViewX1.Rows(inbtIndex).Cells(1).Value & "' , emtypen = '" & DataGridViewX1.Rows(inbtIndex).Cells(2).Value & "' , embookn = '" & DataGridViewX1.Rows(inbtIndex).Cells(3).Value & "' , emscn = '" & DataGridViewX1.Rows(inbtIndex).Cells(4).Value & "' , embyn = '" & DataGridViewX1.Rows(inbtIndex1).Cells(5).Value & "', emremarkn = '" & DataGridViewX1.Rows(inbtIndex).Cells(6).Value & "' WHERE idempty_detail = '" & DataGridViewX1.Rows(inbtIndex).Cells(10).Value & "'; "
                mySqlCommand.CommandText = commandText2
                mySqlCommand.CommandType = CommandType.Text
                mySqlCommand.Connection = mysql
                mySqlCommand.ExecuteNonQuery()
                mysql.Close()
                MsgBox("แก้ไขข้อมูลเรียบร้อย")
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try

            mysql.Close()



        End If

        showgrid()


    End Sub



    Private Sub DELETE_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles DELETE.Click
        Dim respone As Object
        respone = MsgBox("ต้องการลบใช่หรือไม่", MsgBoxStyle.OkCancel + MsgBoxStyle.Information, "Warning Messsage")
        If respone = 1 Then
            mysql.Close()

            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            Try

                mySqlCommand.CommandText = "DELETE FROM voyage_detailn where idvoyage_detailn = '" & DataGridViewX1.Rows(inbtIndex).Cells(10).Value & "';"
                mySqlCommand.CommandType = CommandType.Text
                mySqlCommand.Connection = mysql

                mySqlCommand.ExecuteNonQuery()
                mysql.Close()


            Catch ex As Exception

                MsgBox(ex.ToString)
                Exit Sub
            End Try
            mysql.Close()


        End If
        showgrid()



    End Sub
End Class